if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_schedule_gui_lv')
          and type in ('P','PC'))
   drop procedure bd_proc_schedule_gui_lv
go


create procedure bd_proc_schedule_gui_lv (@user_serial_str varchar(max),@begin_date date,@end_date date,@shift_id varchar(50),@ip varchar(max),@operator varchar(max)) as
BEGIN
-- 关闭打印受影响行数
	SET NOCOUNT ON;
-- 定义返回受影响行数结果
	declare @result int = 0;
	--  异常扑捉机制
	BEGIN TRY
			-- 创建用户数据表
			CREATE TABLE #user(
				id int identity,
				user_serial varchar(200) COLLATE Chinese_PRC_CI_AS,
			)

			-- 规律班次转变的普通班次临时表
			CREATE TABLE #temp(
				id int IDENTITY,
				shift VARCHAR(100) COLLATE Chinese_PRC_CI_AS,
				week  INT, --星期几  1-----7
				days int --当月几号（规律班次是月时使用）
			)
			--排班日期表（每天一条，）
			CREATE TABLE #sechduleDate(
				id int IDENTITY,
				date DATE,
				date_day  INT,
				date_week INT,
				shift VARCHAR(100) COLLATE Chinese_PRC_CI_AS,
				shift_bh  VARCHAR(50)	COLLATE Chinese_PRC_CI_AS,
				shift_is_rest  INT DEFAULT 0
			)
			
						--创建 返回不满足排班的人的原因
			create table #return_table(
				user_serial VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
				user_no VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
				user_name VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
				dept_name VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
				error_msg VARCHAR(200) COLLATE Chinese_PRC_CI_AS
			)
			
			CREATE TABLE #result(
			  user_no varchar(200) COLLATE Chinese_PRC_CI_AS,
			  user_name varchar(200) COLLATE Chinese_PRC_CI_AS,
			  user_serial varchar(200) COLLATE Chinese_PRC_CI_AS,
			  dept_name varchar(200) COLLATE Chinese_PRC_CI_AS,
			  dept_serial varchar(200) COLLATE Chinese_PRC_CI_AS,
			  shift_id int,
			  shift_bh VARCHAR(200)	COLLATE Chinese_PRC_CI_AS,
			  col_name VARCHAR(20) COLLATE Chinese_PRC_CI_AS,
			  value VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
			  date DATE,
			  year_month VARCHAR(10) COLLATE Chinese_PRC_CI_AS,
			  shift_is_rest int
			)
			-- 插入排班
			CREATE TABLE #result_insert(
			  user_serial varchar(200) COLLATE Chinese_PRC_CI_AS,
			  shift_id int,
			  col_name VARCHAR(20) COLLATE Chinese_PRC_CI_AS,
			  value VARCHAR(100) COLLATE Chinese_PRC_CI_AS,
			  date DATE,
			  year_month VARCHAR(10) COLLATE Chinese_PRC_CI_AS
			)
			--更新排班
			CREATE TABLE #result_update(
			  user_serial varchar(200) COLLATE Chinese_PRC_CI_AS,
			  shift_id int,
			  col_name VARCHAR(20) COLLATE Chinese_PRC_CI_AS,
			  value VARCHAR(100) COLLATE Chinese_PRC_CI_AS,
			  date DATE,
			  year_month VARCHAR(10) COLLATE Chinese_PRC_CI_AS
			)
			--临时排班表
			CREATE TABLE #kt_paiba (
			  user_serial varchar(200) COLLATE Chinese_PRC_CI_AS,
			  rq nvarchar(10) COLLATE Chinese_PRC_CI_AS,
			  d1 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d2 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d3 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d4 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d5 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d6 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d7 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d8 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d9 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d10 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d11 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d12 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d13 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d14 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d15 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d16 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d17 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d18 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d19 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d20 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d21 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d22 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d23 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d24 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d25 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d26 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d27 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d28 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d29 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d30 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d31 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  gly_no nvarchar(10) COLLATE Chinese_PRC_CI_AS,
			  year int ,
			  month int
			)
			
			-- 将规律班次放入普通班次临时表
			insert into #temp(shift)
			SELECT
				bc.value + '|' + ban.mc
			FROM (
				SELECT
				v.value
				,iif(isnull(v.value,'')='','',SUBSTRING(v.value, 4, LEN(v.value) - 3)) bh
				FROM zt_glbc t
				CROSS APPLY STRING_SPLIT(CAST(t.Valuse AS VARCHAR(MAX)), ',') v
				WHERE bh = @shift_id
			) bc
			LEFT JOIN zt_banci ban
			ON ban.bh = bc.bh
			DECLARE @c INT, @ts INT, @lx INT, @shiftCount INT

			SELECT @ts=ts,@lx = lx FROM zt_glbc WHERE bh=@shift_id

			-- lx: 0 日，1 周， 2 月

			-- 规律班次天数
			IF (@lx = 0)
				SET @shiftCount = @ts
			ELSE
			IF (@lx = 1)
				SET @shiftCount = 7 * @ts
			ELSE
			IF (@lx = 2)
				SET @shiftCount = 31

			-- 规律班次记录数,
			SELECT @c=COUNT(1) FROM #temp

			IF(@c < @shiftCount)
			BEGIN
				-- 少于规律班次天数，补齐记录数量,这里就是补末尾没有班次也没有设为休息日的那些日期，中间的有空字符串。

				DECLARE @diff INT, @i INT = 0

				SET @diff = @shiftCount - @c

				WHILE @i < @diff
				BEGIN
					INSERT INTO #temp (shift) VALUES('');
					SET @i+=1
				END
			END
			
			
			IF (@lx = 1)
			BEGIN
				--规律（周） 开始是星期1   
				--#temp 直到#temp的每条数据都更新  
				DECLARE @weekInt int=1 --对应的周几  星期一是1 
				SELECT @c=COUNT(1) FROM #temp
				set @i = 1
				while @i<=@c
					BEGIN
						update #temp set week = @weekInt where id = @i;
						
						set @i = @i+1;
						set @weekInt = @weekInt+1;
						if @weekInt=8
							set @weekInt = 1
					END
			end 
			IF (@lx = 2)
			BEGIN
				--规律（月） 开始是1号   
				--#temp 直到#temp的每条数据都更新  
				SELECT @c=COUNT(1) FROM #temp
				set @i = 1
				while @i<=@c
					BEGIN
						update #temp set days = @i where id = @i;
						set @i = @i+1;
					END
			end 
			
			--循环生成日期
			Declare @oneDate Date = @begin_date
			while @oneDate<=@end_date
				BEGIN
					insert into #sechduleDate(date,date_week,date_day) 
					VALUES(
					@oneDate,
					case when DatePart(weekday,@oneDate)-1=0 then 7 else DatePart(weekday,@oneDate)-1 end,
					DatePart(day,@oneDate)
					);
					set @oneDate = dateadd(day,1,@oneDate)  
				END
			
			--lx=0 规律日
			IF (@lx = 0)
				BEGIN
					--规律日：所选日期全部排班，且就一个班次
					update #sechduleDate set shift = (select shift from #temp);
				END
			--lx=0 规律周
			IF (@lx = 1)
				BEGIN
				--规律周：需确定排班的第一天是星期几，则从#temp 第一个出现改星期几的位置向下滚动游标插入数据，每次游标执行完，就检测#sechduleDate插入的位置，
				--如果还没有到最后，则在启动一个游标继续插。
				--DECLARE glbc CURSOR LOCAL  FOR select_statement 
				DECLARE @flag int = 1 --循环控制标识
				DECLARE @rowInt int = 1 --更新的行号
				DECLARE @rowTotal int --#sechduleDate总记录数
				select @rowTotal = count(1) from #sechduleDate
				
				DECLARE @cursorShift varchar(20) --glbc游标中的班次
				DECLARE @cursorWeek int --#glbc游标中的星期数
				DECLARE @fristWeek int --#sechduleDate第一条的星期数
				DECLARE @findFrist int = 1 -- 下方查找第一个匹配的星期数开始更新时使用，找到之后，将该值设置为0.
				select @fristWeek = date_week from #sechduleDate where id = 1;
				while  @flag = 1
					BEGIN
						--当@rowInt 大于等于#sechduleDate的记录数@rowTotal时，将 @flag设为0 停止循环。
						
						--创建游标
						declare glbc cursor LOCAL for
						select shift,week from #temp
						
						open glbc
						
						fetch next from glbc into @cursorShift,@cursorWeek
						
						while @@FETCH_STATUS=0
						begin
								--第一次游标遍历，跳过星期数不匹配的记录，直到第一个匹配开始
								if @findFrist = 1 and @fristWeek != @cursorWeek 
								BEGIN
										print @cursorWeek;
								END
								else BEGIN
									--设置为0，表示已经找到匹配的了。
									set @findFrist = 0;
									update #sechduleDate set shift = @cursorShift where id = @rowInt;
									set @rowInt = @rowInt+1;
								END
								fetch next from glbc into  @cursorShift,@cursorWeek
						end
						
						close glbc
						deallocate glbc
						--如果所有记录都更新完毕，结束外部循环
						if @rowInt-1 >= @rowTotal
							set @flag = 0
					END
				END
			--lx=0 规律月
			IF (@lx = 2)
				BEGIN
					--规律月：1-31不会超过31，就是最大的一个月的天数，所以只要‘日’对应就行。
					update d 
						set shift = t.shift
					from #sechduleDate d 
					INNER JOIN #temp t on t.days = DatePart(day,d.date);
				END
			
			-- 更新班次编号，是否为休息日
			UPDATE #sechduleDate SET shift_bh=CASE WHEN ISNULL(shift,'') <> '' THEN SUBSTRING(shift, 4,CHARINDEX('|',shift)-3-1) ELSE '' END,shift_is_rest = CASE WHEN left(shift,3) = '[X]' THEN 1 ELSE 0 END


			-- 拆分用户编号保存临时表中
			insert into #user(user_serial)
			select value from string_split(iif(isnull(@user_serial_str,'')='','',@user_serial_str),',');

			-- 封装数据
			insert into #result(user_serial,shift_bh,col_name,value,date,year_month,shift_is_rest)
			select a.user_serial,b.shift_bh,concat('d',b.date_day),b.shift,b.date,convert(varchar(7),b.date,120),b.shift_is_rest
			from #user a,#sechduleDate b


			-- 更新数据的排班ID
			update a set a.shift_id = b.id
			from #result a
			inner join zt_banci b on a.shift_bh = b.bh


			-- 更新是否为休息日(一定要在更新kt_paiba表前，删除bd_kq_schedule_detail表数据前)
			update a set a.shift_is_rest = 1,a.value = concat('[X]',substring(a.value,4,len(a.value)))
			from #result a
			inner join (
				-- 两个集合总和的rest大于1 说明一定要是休息日(规律班次)
				select a.user_serial,a.date
				from #result a
				inner join bd_kq_schedule_detail b on a.date = b.shift_date and a.user_serial = b.user_serial
				where (isnull(a.shift_is_rest,0) + isnull(b.shift_is_rest,0)) >= 1
			) b on a.user_serial = b.user_serial and a.date = b.date


			-- 更新人员信息
			update a set a.user_no = isnull(b.user_no,''), a.user_name = isnull(b.user_lname,''), a.dept_name = isnull(c.dep_name,''), a.dept_serial = isnull(c.dep_serial,'')
			from #result a
			left join dt_user b on a.user_serial = b.user_serial
			left join dt_dep c on b.user_dep = c.dep_serial

			
-- 检测是否为封账状态
			insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
			select distinct a.user_serial,a.user_no,a.user_name,a.dept_name,concat(@begin_date,' —— ',@end_date,' 该时段考勤存在已经封账,不能排班')
			from #result a
			inner join bd_kq_accounts c on a.user_serial = c.user_serial and year(a.date) = c.year and month(a.date) = c.month

			-- 删除已经封账的数据
			delete a
			from #result a
			inner join #return_table b on a.user_serial = b.user_serial

					-- 检测非弹性人员排弹性班次
-- 					DECLARE @isTx int = 0
--
--
-- 					select @isTx = sum(is_flextime) from zt_shid s INNER JOIN zt_banci b on s.parent = b.bh
-- 					where b.id in (
-- 						select shift_id from #result
-- 					);
-- 					insert into aa(name)values( @shift_id);
-- 					if(@isTx>=1)
-- 							begin
-- 								insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
-- 								select distinct a.user_serial,a.user_no,a.user_name,a.dept_name,concat(@begin_date,' —— ',@end_date,'非弹性人员不能排弹性班次(规律班次中含有弹性班次)')
-- 								from #result a
-- 								inner join dt_user c on a.user_serial = c.user_serial
-- 								where isnull(c.kqdkLx,0) != 21;
--
-- 								-- 删除非弹性人员排弹性班次
-- 								delete a
-- 								from #result a
-- 								inner join #return_table b on a.user_serial = b.user_serial;
--
-- 							end


					-- 需要新增的数据 #result_insert
				insert into #result_insert(user_serial,shift_id,col_name,value,date,year_month)
				select a.user_serial,a.shift_id,a.col_name,a.value,a.date,a.year_month
				from #result a
				inner join (
					select user_serial,convert(varchar(7),date,120) year_month from #result
					except
					select user_serial,rq as year_month from kt_paiba
				) b on a.user_serial = b.user_serial and a.year_month = b.year_month


				-- 需要修改的数据 #result_update
				insert into #result_update(user_serial,shift_id,col_name,value,date,year_month)
				select a.user_serial,a.shift_id,a.col_name,a.value,a.date,a.year_month
				from #result a
				inner join (
					select user_serial,convert(varchar(7),date,120) year_month from #result
					Intersect
					select user_serial,rq as year_month from kt_paiba
				) b on a.user_serial = b.user_serial and a.year_month = b.year_month



				-- 将插入的数据存入到临时排班表中
				-- 下面两段代码将竖着的数据转为了横着的数据
				INSERT INTO #kt_paiba (user_serial, rq, gly_no, year, month)
				SELECT distinct a.user_serial,CONVERT(VARCHAR(7),date,120),@operator,YEAR(date),MONTH(date)
				FROM #result_insert a


				-- 循环拼接需要更新临时表中的字段
				-- 查询（循环）拼接数据（竖着循环）
				DECLARE @update_insert VARCHAR(MAX)=''
				SELECT @update_insert+='UPDATE #kt_paiba SET '+col_name+'='''+isnull(value,'')+''',gly_no = '''+@operator+''' WHERE rq='''+year_month+''' AND user_serial='+CAST(a.user_serial AS VARCHAR(10))+';'
				FROM #result_insert a

				-- 执行拼接的语句
				EXEC(@update_insert)
				set @result = @result + @@ROWCOUNT;

				-- 从临时表插入到真实表中
				insert into kt_paiba
				select * from #kt_paiba


				-- 需要修改的排班（那些存在的数据）
				DECLARE @update_update VARCHAR(MAX)=''
				SELECT @update_update+='UPDATE kt_paiba SET '+col_name+'='''+isnull(value,'')+''',gly_no = '''+@operator+''' WHERE rq='''+year_month+''' AND user_serial='+CAST(user_serial AS VARCHAR(10))+';'
				FROM #result_update a


				-- 执行拼接的SQL
				EXEC(@update_update)
				set @result = @result + @@ROWCOUNT;


				-- 删除排班纵向数据表
				delete a
				from bd_kq_schedule_detail a
				inner join #result b on a.user_serial = b.user_serial and a.shift_date = b.date


				-- 插入数据详情表
				insert into bd_kq_schedule_detail(user_serial,shift_id,shift_name,shift_date,shift_begin,shift_end,shift_sign_begin,shift_sign_end,shift_late,shift_early,shift_duty_on_delay,shift_duty_off_delay,shift_workday,shift_workday_hours,is_enjoyment_subsidized,middle_shift_start_time,middle_shift_end_time,night_shift_start_time,night_shift_end_time,repast_start_time,repast_end_time,repast_start_time2,repast_end_time2,shift_is_rest)
				select a.user_serial,a.shift_id,a.value,a.date,isnull(c.sbsj,0),isnull(c.xbsj,0),isnull(c.sbks,0),isnull(c.xbjs,0),isnull(c.cdsj,0),isnull(c.ztsj,0),isnull(c.bjcd,0),isnull(c.bjzt,0),isnull(c.alld,0),isnull(c.allt,0),isnull(c.is_enjoyment_subsidized,0),isnull(c.middle_shift_start_time,0),isnull(c.middle_shift_end_time,0),isnull(c.night_shift_start_time,0),isnull(c.night_shift_end_time,0),isnull(repast_start_time,0),isnull(c.repast_end_time,0),isnull(repast_start_time2,0),isnull(c.repast_end_time2,0),a.shift_is_rest
				from #result a
				inner join zt_banci b on a.shift_id = b.id
				inner join zt_shid c on b.bh = c.parent
				where c.lx = 0



				-- 新增的生成日志记录
				insert into wt_log(log_fun,log_type,log_detail,gly_no,log_time,log_computer,log_ip,regserial)
				select distinct 13,1,isnull(@operator,'')+'录入'+isnull(b.user_lname,'')+isnull(a.year_month,'')+'排班',isnull(@operator,''),getdate(),'',isnull(@ip,''),''
				from #result_insert a
				inner join dt_user b on a.user_serial = b.user_serial


				--更新的生成日志记录
				insert into wt_log(log_fun,log_type,log_detail,gly_no,log_time,log_computer,log_ip,regserial)
				select distinct 13,1,isnull(@operator,'')+'更新'+isnull(b.user_lname,'')+isnull(a.year_month,'')+'排班',isnull(@operator,''),getdate(),'',isnull(@ip,''),''
				from #result_update a
				inner join dt_user b on a.user_serial = b.user_serial

				--返回
				select * from #return_table
				-- 调用考勤计算(排班4)
				EXEC bd_proc_timecard_extend @begin_date,@end_date,@user_serial_str,4

		--  提交事务
			COMMIT TRAN

	--  结束异常捕捉
	END TRY

	--  异常处理
	BEGIN CATCH
		DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
		IF XACT_STATE() <> 0
		BEGIN
			ROLLBACK TRANSACTION;
			RAISERROR (@errStr, 16, 1);
		END;
		ROLLBACK TRAN;
	END CATCH


	

--  清除临时表
	DROP table #user
	DROP TABLE #temp
	DROP TABLE #shift
	DROP TABLE #result
	DROP TABLE #result_insert
	DROP TABLE #result_update
	DROP TABLE #sechduleDate
	DROP TABLE #return_table
  DROP TABLE #kt_paiba

	-- 打开打印受影响行数
	SET NOCOUNT OFF;			
		
END
go
